package com.liyueheng.app.dataWarehouseDetail.ranking

import com.liyueheng.util.{ConfigLoader, SaveAsTable, SparkConf}
import org.apache.spark.sql.functions._

object Top10FemaleSpenders {
  def stat(): Unit = {
    println("------------------ 分析女性消费前十名 -----------------")
    val spark = SparkConf.createSparkSession("Top10FemaleSpenders")
    val dwd = ConfigLoader.getString("databases.dwd")
    val dws = ConfigLoader.getString("databases.dws")

    val user = spark.table(s"$dwd.user")
    val act = spark.table(s"$dwd.user_act").filter("act_type = 2")
    val sku = spark.table(s"$dwd.sku")

    val result = act.join(user, act("user") === user("id"))
      .join(sku, act("sku") === sku("sku_id"))
      .filter("gender = 'female'")
      .groupBy("id")
      .agg(round(sum("price"), 2).alias("value"))
      .withColumnRenamed("id", "name")
      .orderBy(desc("value"))
      .limit(10)

    SaveAsTable.saveAsTable(result, s"$dws.detail_top10_female_spenders")
    SparkConf.stopSparkSession(spark)
  }
}
